Performance/tuning newbie
So a friend of mine runs a mildly successful WP knife review blog (bladereviews.com) and he's starting to outgrow his $5/mo GoDaddy hosting, so I told him I have a VPS @ Linode that should handle it and he was all for it. Well, we switched his DNS over last night for a trial run and, while I wasn't expecting to set any time to first byte page load records, I was taken rather by surprise when my Linode's resources were completely tapped. Even slower page loads, DB timeouts and I couldn't even reach other services running on the server (SSH, FTP, etc). Needless to say we switched his DNS back over to minimize downtime.
I'm not implying that this is anyone's fault but my own here - I know my way around a Linux server to be adequate in some scenarios but I've never had to think about or deal with performance optimization. Some low-hanging fruit seemed to already be implemented in the default install of Apache (output compression, for example), so I didn't try any further tweaking beforehand. It seems I should have ><
Anyway, what I'm looking for is some insight into what the bottleneck(s) might be and what I might be able to do to make things better. I did have a peek at the troubleshooting guide after the fact; unfortunately, at the time, I wouldn't have been able to even use most of those utilities as I couldn't SSH in. I'm thinking at the very least I'll need to increase the size of my swap partition (currently 512), perhaps move to a different web server (lighthttpd, nginx), and configure mysql to connect via socket but, like I said, this is all just speculation at this point on my part.
Let me know what logs/configs I should post, and I will oblige.
Here's a screenshot of my friend's traffic from Google Analytics, to get an idea:
~~http://i.imgur.com/kPGcx.jpg
Many thanks,
rawsted~~
14 Replies
Did you run Apache with modphp and default configurations for mpmprefork? That's a recipe for disaster. The common symptom of using the default config (MaxClients 150) is an extremely slow server that you can't even SSH into. Looks like this fits your description. Fix this first.
Tune the hell out of MySQL, using mysqltuner (Google it). I don't know which distro you were using, but default configurations are often inadequate. On the other hand, MySQL on "localhost" almost always connects by socket by default. No need to do anything extra there unless you're using an unusual distro.
Don't increase swap. Instead, decrease it to 256MB. Large swap is OK for desktop computers, but it's no good on a VPS.
Don't change DNS until you're sure it's working. You can test your site without changing DNS, by playing with the hosts file on your own computer. The exact method depends on your OS, but it's all on Google.
@hybinet:
Did you run Apache with modphp and default configurations for mpmprefork? That's a recipe for disaster. The common symptom of using the default config (MaxClients 150) is an extremely slow server that you can't even SSH into. Looks like this fits your description. Fix this first.
MaxClients in Apache config or? Otherwise, how would this affect other services?
I am using mpm_prefork with defaults. Is there anything else I should change about this?
@hybinet:
Tune the hell out of MySQL, using mysqltuner (Google it).
I'll check it out.
@hybinet:
I don't know which distro you were using
Debian
@hybinet:
…but default configurations are often inadequate. On the other hand, MySQL on "localhost" almost always connects by socket by default. No need to do anything extra there unless you're using an unusual distro.
Ah, OK.
@hybinet:
Don't increase swap. Instead, decrease it to 256MB. Large swap is OK for desktop computers, but it's no good on a VPS.
Really? Why is that?
@hybinet:
Don't change DNS until you're sure it's working. You can test your site without changing DNS, by playing with the hosts file on your own computer. The exact method depends on your OS, but it's all on Google.
I know about the local hosts file, but what's a good way to do load testing from a single, or perhaps 2 machines?
@rawsted:
MaxClients in Apache config or? Otherwise, how would this affect other services?
I am using mpm_prefork with defaults. Is there anything else I should change about this?
Yes, you should edit /etc/apache2/apache2.conf
ServerLimit and MaxClients should be less than 20.
Disable KeepAlive or make it really low, like 2.
This will prevent Apache from gobbling up all your RAM. It will NOT affect any other services on your server.
@rawsted:
@hybinet:Don't increase swap.
Really? Why is that?
Swap is really slow in a shared environment. Some people treat swap as "burstable RAM", but that doesn't work here. RAM is RAM and swap is swap. They're not exchangeable. It's OK to let the kernel swap out a few MB of idle stuff from time to time, but you don't want your critical apps (like Apache, MySQL, and SSH) to touch swap with a 10ft pole, because as soon as they hit swap, they'll stop responding.
@rawsted:
I know about the local hosts file, but what's a good way to do load testing from a single, or perhaps 2 machines?
If you just want to know how your server behaves when a few dozen people visit it at the same time, use ab (it's in the apache2-utils package). Closely monitor memory usage in another terminal window. RAM is the most precious resource in a VPS.
I've implemented the following changes in apache2.conf/prefork:
StartServers 1
MinSpareServers 3
MaxSpareServers 6
ServerLimit 24
MaxClients 24
MaxRequestsPerChild 3000
Per
Using ab -n 200 -c 4 [
~~[code]~~key_buffer = 16K max_allowed_packet = 1M thread_stack = 64K table_cache = 4 sort_buffer = 64K net_buffer_length = 2K<e>[/code]</e> Here's my full mysql config, followed by what mysqltuner is currently saying:
~~[code]~~mysql> SHOW VARIABLES; +-----------------------------------------+-------------------------------------------------------------------------------------------+ | Variable_name | Value | +-----------------------------------------+-------------------------------------------------------------------------------------------+ | auto_increment_increment | 1 | | auto_increment_offset | 1 | | autocommit | ON | | automatic_sp_privileges | ON | | back_log | 50 | | basedir | /usr/ | | big_tables | OFF | | binlog_cache_size | 32768 | | binlog_direct_non_transactional_updates | OFF | | binlog_format | STATEMENT | | bulk_insert_buffer_size | 8388608 | | character_set_client | latin1 | | character_set_connection | latin1 | | character_set_database | latin1 | | character_set_filesystem | binary | | character_set_results | latin1 | | character_set_server | latin1 | | character_set_system | utf8 | | character_sets_dir | /usr/share/mysql/charsets/ | | collation_connection | latin1_swedish_ci | | collation_database | latin1_swedish_ci | | collation_server | latin1_swedish_ci | | completion_type | 0 | | concurrent_insert | 1 | | connect_timeout | 10 | | datadir | /var/lib/mysql/ | | date_format | %Y-%m-%d | | datetime_format | %Y-%m-%d %H:%i:%s | | default_week_format | 0 | | delay_key_write | ON | | delayed_insert_limit | 100 | | delayed_insert_timeout | 300 | | delayed_queue_size | 1000 | | div_precision_increment | 4 | | engine_condition_pushdown | ON | | error_count | 0 | | event_scheduler | OFF | | expire_logs_days | 10 | | flush | OFF | | flush_time | 0 | | foreign_key_checks | ON | | ft_boolean_syntax | + -><()~*:""&| | | ft_max_word_len | 84 | | ft_min_word_len | 4 | | ft_query_expansion_limit | 20 | | ft_stopword_file | (built-in) | | general_log | OFF | | general_log_file | /var/run/mysqld/mysqld.log | | group_concat_max_len | 1024 | | have_community_features | YES | | have_compress | YES | | have_crypt | YES | | have_csv | YES | | have_dynamic_loading | YES | | have_geometry | YES | | have_innodb | DISABLED | | have_ndbcluster | NO | | have_openssl | DISABLED | | have_partitioning | YES | | have_query_cache | YES | | have_rtree_keys | YES | | have_ssl | DISABLED | | have_symlink | YES | | hostname | daedalus | | identity | 0 | | ignore_builtin_innodb | OFF | | init_connect | | | init_file | | | init_slave | | | insert_id | 0 | | interactive_timeout | 28800 | | join_buffer_size | 131072 | | keep_files_on_create | OFF | | key_buffer_size | 16384 | | key_cache_age_threshold | 300 | | key_cache_block_size | 1024 | | key_cache_division_limit | 100 | | language | /usr/share/mysql/english/ | | large_files_support | ON | | large_page_size | 0 | | large_pages | OFF | | last_insert_id | 0 | | lc_time_names | en_US | | license | GPL | | local_infile | ON | | locked_in_memory | OFF | | log | OFF | | log_bin | OFF | | log_bin_trust_function_creators | OFF | | log_bin_trust_routine_creators | OFF | | log_error | | | log_output | FILE | | log_queries_not_using_indexes | OFF | | log_slave_updates | OFF | | log_slow_queries | ON | | log_warnings | 1 | | long_query_time | 10.000000 | | low_priority_updates | OFF | | lower_case_file_system | OFF | | lower_case_table_names | 0 | | max_allowed_packet | 1048576 | | max_binlog_cache_size | 18446744073709547520 | | max_binlog_size | 104857600 | | max_connect_errors | 10 | | max_connections | 151 | | max_delayed_threads | 20 | | max_error_count | 64 | | max_heap_table_size | 16777216 | | max_insert_delayed_threads | 20 | | max_join_size | 18446744073709551615 | | max_length_for_sort_data | 1024 | | max_prepared_stmt_count | 16382 | | max_relay_log_size | 0 | | max_seeks_for_key | 18446744073709551615 | | max_sort_length | 1024 | | max_sp_recursion_depth | 0 | | max_tmp_tables | 32 | | max_user_connections | 0 | | max_write_lock_count | 18446744073709551615 | | min_examined_row_limit | 0 | | multi_range_count | 256 | | myisam_data_pointer_size | 6 | | myisam_max_sort_file_size | 9223372036853727232 | | myisam_mmap_size | 18446744073709551615 | | myisam_recover_options | BACKUP | | myisam_repair_threads | 1 | | myisam_sort_buffer_size | 8388608 | | myisam_stats_method | nulls_unequal | | myisam_use_mmap | OFF | | net_buffer_length | 2048 | | net_read_timeout | 30 | | net_retry_count | 10 | | net_write_timeout | 60 | | new | OFF | | old | OFF | | old_alter_table | OFF | | old_passwords | OFF | | open_files_limit | 1024 | | optimizer_prune_level | 1 | | optimizer_search_depth | 62 | | optimizer_switch | index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on | | pid_file | /var/run/mysqld/mysqld.pid | | plugin_dir | /usr/lib/mysql/plugin | | port | 3306 | | preload_buffer_size | 32768 | | profiling | OFF | | profiling_history_size | 15 | | protocol_version | 10 | | pseudo_thread_id | 2039 | | query_alloc_block_size | 8192 | | query_cache_limit | 1048576 | | query_cache_min_res_unit | 4096 | | query_cache_size | 16777216 | | query_cache_type | ON | | query_cache_wlock_invalidate | OFF | | query_prealloc_size | 8192 | | rand_seed1 | | | rand_seed2 | | | range_alloc_block_size | 4096 | | read_buffer_size | 131072 | | read_only | OFF | | read_rnd_buffer_size | 262144 | | relay_log | | | relay_log_index | | | relay_log_info_file | relay-log.info | | relay_log_purge | ON | | relay_log_space_limit | 0 | | report_host | | | report_password | | | report_port | 3306 | | report_user | | | rpl_recovery_rank | 0 | | secure_auth | OFF | | secure_file_priv | | | server_id | 0 | | skip_external_locking | ON | | skip_name_resolve | OFF | | skip_networking | OFF | | skip_show_database | OFF | | slave_compressed_protocol | OFF | | slave_exec_mode | STRICT | | slave_load_tmpdir | /tmp | | slave_net_timeout | 3600 | | slave_skip_errors | OFF | | slave_transaction_retries | 10 | | slow_launch_time | 2 | | slow_query_log | ON | | slow_query_log_file | /var/log/mysql/mysql-slow.log | | socket | /var/run/mysqld/mysqld.sock | | sort_buffer_size | 32776 | | sql_auto_is_null | ON | | sql_big_selects | ON | | sql_big_tables | OFF | | sql_buffer_result | OFF | | sql_log_bin | ON | | sql_log_off | OFF | | sql_log_update | ON | | sql_low_priority_updates | OFF | | sql_max_join_size | 18446744073709551615 | | sql_mode | | | sql_notes | ON | | sql_quote_show_create | ON | | sql_safe_updates | OFF | | sql_select_limit | 18446744073709551615 | | sql_slave_skip_counter | | | sql_warnings | OFF | | ssl_ca | | | ssl_capath | | | ssl_cert | | | ssl_cipher | | | ssl_key | | | storage_engine | MyISAM | | sync_binlog | 0 | | sync_frm | ON | | system_time_zone | EST | | table_definition_cache | 256 | | table_lock_wait_timeout | 50 | | table_open_cache | 4 | | table_type | MyISAM | | thread_cache_size | 8 | | thread_handling | one-thread-per-connection | | thread_stack | 131072 | | time_format | %H:%i:%s | | time_zone | SYSTEM | | timed_mutexes | OFF | | timestamp | 1326207155 | | tmp_table_size | 16777216 | | tmpdir | /tmp | | transaction_alloc_block_size | 8192 | | transaction_prealloc_size | 4096 | | tx_isolation | REPEATABLE-READ | | unique_checks | ON | | updatable_views_with_limit | YES | | version | 5.1.49-3-log | | version_comment | (Debian) | | version_compile_machine | x86_64 | | version_compile_os | debian-linux-gnu | | wait_timeout | 28800 | | warning_count | 0 | +-----------------------------------------+-------------------------------------------------------------------------------------------+ 239 rows in set (0.06 sec)<e>[/code]</e> ~~[code]~~-------- General Statistics -------------------------------------------------- [--] Skipped version check for MySQLTuner script [OK] Currently running supported MySQL version 5.1.49-3-log [OK] Operating on 64-bit architecture -------- Storage Engine Statistics ------------------------------------------- [--] Status: -Archive -BDB -Federated -InnoDB -ISAM -NDBCluster [--] Data in MyISAM tables: 85M (Tables: 185) [OK] Total fragmented tables: 0 -------- Performance Metrics ------------------------------------------------- [--] Up for: 23h 36m 44s (17K q [0.205 qps], 2K conn, TX: 462M, RX: 1M) [--] Reads / Writes: 87% / 13% [--] Total buffers: 32.0M global + 672.0K per thread (151 max threads) [OK] Maximum possible memory usage: 131.1M (30% of installed RAM) [OK] Slow queries: 0% (62/17K) [OK] Highest usage of available connections: 26% (40/151) [!!] Key buffer size / total MyISAM indexes: 16.0K/39.0M [!!] Key buffer hit rate: 85.4% (10K cached / 1K reads) [OK] Query cache efficiency: 79.3% (8K cached / 10K selects) [OK] Query cache prunes per day: 0 [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 74 sorts) [!!] Temporary tables created on disk: 35% (173 on disk / 493 total) [OK] Thread cache hit rate: 96% (72 created / 2K connections) [!!] Table cache hit rate: 0% (4 open / 3K opened) [OK] Open file limit used: 0% (9/1K) [!!] Table locks acquired immediately: 93% -------- Recommendations ----------------------------------------------------- General recommendations: MySQL started within last 24 hours - recommendations may be inaccurate When making adjustments, make tmp_table_size/max_heap_table_size equal Reduce your SELECT DISTINCT queries without LIMIT clauses Increase table_cache gradually to avoid file descriptor limits Optimize queries and/or use InnoDB to reduce lock wait Variables to adjust: key_buffer_size (> 39.0M) tmp_table_size (> 16M) max_heap_table_size (> 16M) table_cache (> 4)<e>[/code]</e> I don't really know what these directives control. I'm interested in the community's interpretation before going ahead with them, as well as where I might go from here, in general (APC, FastCGI?).
Thanks,
rawsted
Edit:
+information](, I'm now seeing about 13.77 reqs/sec which, on the surface, sounds good but there are some things that concern me:
1) The 'Document Length:' field in the ab output is '0 bytes'. The FQDN is correctly pointing at the Linode in /etc/hosts, and if I use a console browser, the right site loads. I know this because I intentionally misconfigured wp-config.php, and I saw the error reflected in the console browser. What gives with the 0-byte doc length?
Here's free -m and resource usage during the ab run:
<s>[code]</s> total used free shared buffers cached
Mem: 424 354 70 0 3 41
-/+ buffers/cache: 309 115
Swap: 255 24 231<e>[/code]</e>
<s>[code]</s>%MEM %CPU RSS VSZ COMMAND
9.8 1.7 42940 222872 /usr/sbin/apache2 -k start
9.7 1.7 42292 222064 /usr/sbin/apache2 -k start
9.0 2.2 39252 218864 /usr/sbin/apache2 -k start
8.9 53.3 38984 218648 /usr/sbin/apache2 -k start
8.9 1.6 38896 218904 /usr/sbin/apache2 -k start
8.9 14.2 38984 218608 /usr/sbin/apache2 -k start
2.2 0.0 9628 335816 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --user=mysql --pid-file=/var/run/mysqld/mysqld.pid --socket=/var/run/mysqld/mysqld.sock --port=3306
1.5 0.0 6796 63408 irssi
10.2 2.1 44648 223536 /usr/sbin/apache2 -k start
0.5 0.3 2320 34332 ab -n 200 -c 4 http://www.bladereviews.com/<e>[/code]</e>
(heh, I don't plan to be running irssi in production mode, not to worry)
2) Before running a DB optimize, ab was giving 'aprpoll: The timeout specified has expired (70007)' after completing only tens of requests. I have implemented some changes to my.cnf per [" target=" blank">, I'm now seeing about 13.77 reqs/sec which, on the surface, sounds good but there are some things that concern me:
1) The 'Document Length:' field in the ab output is '0 bytes'. The FQDN is correctly pointing at the Linode in /etc/hosts, and if I use a console browser, the right site loads. I know this because I intentionally misconfigured wp-config.php, and I saw the error reflected in the console browser. What gives with the 0-byte doc length?
Here's free -m and resource usage during the ab run:
total used free shared buffers cached
Mem: 424 354 70 0 3 41
-/+ buffers/cache: 309 115
Swap: 255 24 231
%MEM %CPU RSS VSZ COMMAND
9.8 1.7 42940 222872 /usr/sbin/apache2 -k start
9.7 1.7 42292 222064 /usr/sbin/apache2 -k start
9.0 2.2 39252 218864 /usr/sbin/apache2 -k start
8.9 53.3 38984 218648 /usr/sbin/apache2 -k start
8.9 1.6 38896 218904 /usr/sbin/apache2 -k start
8.9 14.2 38984 218608 /usr/sbin/apache2 -k start
2.2 0.0 9628 335816 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --user=mysql --pid-file=/var/run/mysqld/mysqld.pid --socket=/var/run/mysqld/mysqld.sock --port=3306
1.5 0.0 6796 63408 irssi
10.2 2.1 44648 223536 /usr/sbin/apache2 -k start
0.5 0.3 2320 34332 ab -n 200 -c 4 http://www.bladereviews.com/
(heh, I don't plan to be running irssi in production mode, not to worry)
2) Before running a DB optimize, ab was giving 'apr_poll: The timeout specified has expired (70007)' after completing only tens of requests. I have implemented some changes to my.cnf per http://library.linode.com/troubleshooti … networking">http://library.linode.com/troubleshooting/memory-networking
key_buffer = 16K
max_allowed_packet = 1M
thread_stack = 64K
table_cache = 4
sort_buffer = 64K
net_buffer_length = 2K
Here's my full mysql config, followed by what mysqltuner is currently saying:
mysql> SHOW VARIABLES;
+-----------------------------------------+-------------------------------------------------------------------------------------------+
| Variable_name | Value |
+-----------------------------------------+-------------------------------------------------------------------------------------------+
| auto_increment_increment | 1 |
| auto_increment_offset | 1 |
| autocommit | ON |
| automatic_sp_privileges | ON |
| back_log | 50 |
| basedir | /usr/ |
| big_tables | OFF |
| binlog_cache_size | 32768 |
| binlog_direct_non_transactional_updates | OFF |
| binlog_format | STATEMENT |
| bulk_insert_buffer_size | 8388608 |
| character_set_client | latin1 |
| character_set_connection | latin1 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | latin1 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
| collation_connection | latin1_swedish_ci |
| collation_database | latin1_swedish_ci |
| collation_server | latin1_swedish_ci |
| completion_type | 0 |
| concurrent_insert | 1 |
| connect_timeout | 10 |
| datadir | /var/lib/mysql/ |
| date_format | %Y-%m-%d |
| datetime_format | %Y-%m-%d %H:%i:%s |
| default_week_format | 0 |
| delay_key_write | ON |
| delayed_insert_limit | 100 |
| delayed_insert_timeout | 300 |
| delayed_queue_size | 1000 |
| div_precision_increment | 4 |
| engine_condition_pushdown | ON |
| error_count | 0 |
| event_scheduler | OFF |
| expire_logs_days | 10 |
| flush | OFF |
| flush_time | 0 |
| foreign_key_checks | ON |
| ft_boolean_syntax | + -><()~*:""&| |
| ft_max_word_len | 84 |
| ft_min_word_len | 4 |
| ft_query_expansion_limit | 20 |
| ft_stopword_file | (built-in) |
| general_log | OFF |
| general_log_file | /var/run/mysqld/mysqld.log |
| group_concat_max_len | 1024 |
| have_community_features | YES |
| have_compress | YES |
| have_crypt | YES |
| have_csv | YES |
| have_dynamic_loading | YES |
| have_geometry | YES |
| have_innodb | DISABLED |
| have_ndbcluster | NO |
| have_openssl | DISABLED |
| have_partitioning | YES |
| have_query_cache | YES |
| have_rtree_keys | YES |
| have_ssl | DISABLED |
| have_symlink | YES |
| hostname | daedalus |
| identity | 0 |
| ignore_builtin_innodb | OFF |
| init_connect | |
| init_file | |
| init_slave | |
| insert_id | 0 |
| interactive_timeout | 28800 |
| join_buffer_size | 131072 |
| keep_files_on_create | OFF |
| key_buffer_size | 16384 |
| key_cache_age_threshold | 300 |
| key_cache_block_size | 1024 |
| key_cache_division_limit | 100 |
| language | /usr/share/mysql/english/ |
| large_files_support | ON |
| large_page_size | 0 |
| large_pages | OFF |
| last_insert_id | 0 |
| lc_time_names | en_US |
| license | GPL |
| local_infile | ON |
| locked_in_memory | OFF |
| log | OFF |
| log_bin | OFF |
| log_bin_trust_function_creators | OFF |
| log_bin_trust_routine_creators | OFF |
| log_error | |
| log_output | FILE |
| log_queries_not_using_indexes | OFF |
| log_slave_updates | OFF |
| log_slow_queries | ON |
| log_warnings | 1 |
| long_query_time | 10.000000 |
| low_priority_updates | OFF |
| lower_case_file_system | OFF |
| lower_case_table_names | 0 |
| max_allowed_packet | 1048576 |
| max_binlog_cache_size | 18446744073709547520 |
| max_binlog_size | 104857600 |
| max_connect_errors | 10 |
| max_connections | 151 |
| max_delayed_threads | 20 |
| max_error_count | 64 |
| max_heap_table_size | 16777216 |
| max_insert_delayed_threads | 20 |
| max_join_size | 18446744073709551615 |
| max_length_for_sort_data | 1024 |
| max_prepared_stmt_count | 16382 |
| max_relay_log_size | 0 |
| max_seeks_for_key | 18446744073709551615 |
| max_sort_length | 1024 |
| max_sp_recursion_depth | 0 |
| max_tmp_tables | 32 |
| max_user_connections | 0 |
| max_write_lock_count | 18446744073709551615 |
| min_examined_row_limit | 0 |
| multi_range_count | 256 |
| myisam_data_pointer_size | 6 |
| myisam_max_sort_file_size | 9223372036853727232 |
| myisam_mmap_size | 18446744073709551615 |
| myisam_recover_options | BACKUP |
| myisam_repair_threads | 1 |
| myisam_sort_buffer_size | 8388608 |
| myisam_stats_method | nulls_unequal |
| myisam_use_mmap | OFF |
| net_buffer_length | 2048 |
| net_read_timeout | 30 |
| net_retry_count | 10 |
| net_write_timeout | 60 |
| new | OFF |
| old | OFF |
| old_alter_table | OFF |
| old_passwords | OFF |
| open_files_limit | 1024 |
| optimizer_prune_level | 1 |
| optimizer_search_depth | 62 |
| optimizer_switch | index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on |
| pid_file | /var/run/mysqld/mysqld.pid |
| plugin_dir | /usr/lib/mysql/plugin |
| port | 3306 |
| preload_buffer_size | 32768 |
| profiling | OFF |
| profiling_history_size | 15 |
| protocol_version | 10 |
| pseudo_thread_id | 2039 |
| query_alloc_block_size | 8192 |
| query_cache_limit | 1048576 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 16777216 |
| query_cache_type | ON |
| query_cache_wlock_invalidate | OFF |
| query_prealloc_size | 8192 |
| rand_seed1 | |
| rand_seed2 | |
| range_alloc_block_size | 4096 |
| read_buffer_size | 131072 |
| read_only | OFF |
| read_rnd_buffer_size | 262144 |
| relay_log | |
| relay_log_index | |
| relay_log_info_file | relay-log.info |
| relay_log_purge | ON |
| relay_log_space_limit | 0 |
| report_host | |
| report_password | |
| report_port | 3306 |
| report_user | |
| rpl_recovery_rank | 0 |
| secure_auth | OFF |
| secure_file_priv | |
| server_id | 0 |
| skip_external_locking | ON |
| skip_name_resolve | OFF |
| skip_networking | OFF |
| skip_show_database | OFF |
| slave_compressed_protocol | OFF |
| slave_exec_mode | STRICT |
| slave_load_tmpdir | /tmp |
| slave_net_timeout | 3600 |
| slave_skip_errors | OFF |
| slave_transaction_retries | 10 |
| slow_launch_time | 2 |
| slow_query_log | ON |
| slow_query_log_file | /var/log/mysql/mysql-slow.log |
| socket | /var/run/mysqld/mysqld.sock |
| sort_buffer_size | 32776 |
| sql_auto_is_null | ON |
| sql_big_selects | ON |
| sql_big_tables | OFF |
| sql_buffer_result | OFF |
| sql_log_bin | ON |
| sql_log_off | OFF |
| sql_log_update | ON |
| sql_low_priority_updates | OFF |
| sql_max_join_size | 18446744073709551615 |
| sql_mode | |
| sql_notes | ON |
| sql_quote_show_create | ON |
| sql_safe_updates | OFF |
| sql_select_limit | 18446744073709551615 |
| sql_slave_skip_counter | |
| sql_warnings | OFF |
| ssl_ca | |
| ssl_capath | |
| ssl_cert | |
| ssl_cipher | |
| ssl_key | |
| storage_engine | MyISAM |
| sync_binlog | 0 |
| sync_frm | ON |
| system_time_zone | EST |
| table_definition_cache | 256 |
| table_lock_wait_timeout | 50 |
| table_open_cache | 4 |
| table_type | MyISAM |
| thread_cache_size | 8 |
| thread_handling | one-thread-per-connection |
| thread_stack | 131072 |
| time_format | %H:%i:%s |
| time_zone | SYSTEM |
| timed_mutexes | OFF |
| timestamp | 1326207155 |
| tmp_table_size | 16777216 |
| tmpdir | /tmp |
| transaction_alloc_block_size | 8192 |
| transaction_prealloc_size | 4096 |
| tx_isolation | REPEATABLE-READ |
| unique_checks | ON |
| updatable_views_with_limit | YES |
| version | 5.1.49-3-log |
| version_comment | (Debian) |
| version_compile_machine | x86_64 |
| version_compile_os | debian-linux-gnu |
| wait_timeout | 28800 |
| warning_count | 0 |
+-----------------------------------------+-------------------------------------------------------------------------------------------+
239 rows in set (0.06 sec)
-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.1.49-3-log
[OK] Operating on 64-bit architecture
-------- Storage Engine Statistics -------------------------------------------
[--] Status: -Archive -BDB -Federated -InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 85M (Tables: 185)
[OK] Total fragmented tables: 0
-------- Performance Metrics -------------------------------------------------
[--] Up for: 23h 36m 44s (17K q [0.205 qps], 2K conn, TX: 462M, RX: 1M)
[--] Reads / Writes: 87% / 13%
[--] Total buffers: 32.0M global + 672.0K per thread (151 max threads)
[OK] Maximum possible memory usage: 131.1M (30% of installed RAM)
[OK] Slow queries: 0% (62/17K)
[OK] Highest usage of available connections: 26% (40/151)
[!!] Key buffer size / total MyISAM indexes: 16.0K/39.0M
[!!] Key buffer hit rate: 85.4% (10K cached / 1K reads)
[OK] Query cache efficiency: 79.3% (8K cached / 10K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 74 sorts)
[!!] Temporary tables created on disk: 35% (173 on disk / 493 total)
[OK] Thread cache hit rate: 96% (72 created / 2K connections)
[!!] Table cache hit rate: 0% (4 open / 3K opened)
[OK] Open file limit used: 0% (9/1K)
[!!] Table locks acquired immediately: 93%
-------- Recommendations -----------------------------------------------------
General recommendations:
MySQL started within last 24 hours - recommendations may be inaccurate
When making adjustments, make tmp_table_size/max_heap_table_size equal
Reduce your SELECT DISTINCT queries without LIMIT clauses
Increase table_cache gradually to avoid file descriptor limits
Optimize queries and/or use InnoDB to reduce lock wait
Variables to adjust:
key_buffer_size (> 39.0M)
tmp_table_size (> 16M)
max_heap_table_size (> 16M)
table_cache (> 4)
I don't really know what these directives control. I'm interested in the community's interpretation before going ahead with them, as well as where I might go from here, in general (APC, FastCGI?).
Thanks,
rawsted
Edit:
+information](, I'm now seeing about 13.77 reqs/sec which, on the surface, sounds good but there are some things that concern me:
1) The 'Document Length:' field in the ab output is '0 bytes'. The FQDN is correctly pointing at the Linode in /etc/hosts, and if I use a console browser, the right site loads. I know this because I intentionally misconfigured wp-config.php, and I saw the error reflected in the console browser. What gives with the 0-byte doc length?
Here's free -m and resource usage during the ab run:
<s>[code]</s> total used free shared buffers cached
Mem: 424 354 70 0 3 41
-/+ buffers/cache: 309 115
Swap: 255 24 231<e>[/code]</e>
<s>[code]</s>%MEM %CPU RSS VSZ COMMAND
9.8 1.7 42940 222872 /usr/sbin/apache2 -k start
9.7 1.7 42292 222064 /usr/sbin/apache2 -k start
9.0 2.2 39252 218864 /usr/sbin/apache2 -k start
8.9 53.3 38984 218648 /usr/sbin/apache2 -k start
8.9 1.6 38896 218904 /usr/sbin/apache2 -k start
8.9 14.2 38984 218608 /usr/sbin/apache2 -k start
2.2 0.0 9628 335816 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --user=mysql --pid-file=/var/run/mysqld/mysqld.pid --socket=/var/run/mysqld/mysqld.sock --port=3306
1.5 0.0 6796 63408 irssi
10.2 2.1 44648 223536 /usr/sbin/apache2 -k start
0.5 0.3 2320 34332 ab -n 200 -c 4 http://www.bladereviews.com/<e>[/code]</e>
(heh, I don't plan to be running irssi in production mode, not to worry)
2) Before running a DB optimize, ab was giving 'apr_poll: The timeout specified has expired (70007)' after completing only tens of requests. I have implemented some changes to my.cnf per <URL url=)
Edit:
Figured this out by looking at the 'Location:' response header. His DNS configuration prefers no subdomain; I was using 'www' in my tests. Re-running the benchmark tests still shows 13.x reqs/sec, so that's looking good…really all that's left is to optimize MySQL further; as I said, I'm a bit in the dark here, if anyone has any recommendations based on the above post, I'd be grateful.
MaxClients 24 is too high for a Linode 512. Reduce it to 12-16 so that MySQL can use more RAM. Don't be fooled by the name of this setting. As long as KeepAlive is disabled, you'll be able to serve lots and lots of clients even if MaxClients is low.
Install APC. There's absolutely no reason not to.
As for FastCGI, don't bother with it unless you want to switch to lighttpd or nginx.
Install a caching plugin for WordPress.
Adjust the variables that mysqltuner tells you to adjust. They are meant to give MySQL more memory to play with. This might seem to conflict with the "low memory setting" tutorial you linked to (it tells you to use 16K key_buffer whereas mysqltuner wants 39M+), but the tutorial was written for really low memory situations where you must conserve memory even at the cost of performance. Don't follow the tutorial, follow mysqltuner. After all, you can't expect MySQL to handle a large WordPress database efficiently if it is only allowed to use a few kilobytes of buffers and cache.
Run ab again after increasing these variables as suggested.
@hybinet:
First of all, make sure you're actually benchmarking the Linode instead of the old shared hosting account. WordPress has a nasty tendency to redirect to the URL where it was first installed, if you use any other hostname to access it.
Roger, I've got the right one.
@hybinet:
MaxClients 24 is too high for a Linode 512. Reduce it to 12-16 so that MySQL can use more RAM. Don't be fooled by the name of this setting. As long as KeepAlive is disabled, you'll be able to serve lots and lots of clients even if MaxClients is low.
Bumped down to 16.
@hybinet:
Install APC. There's absolutely no reason not to.
Done.
@hybinet:
As for FastCGI, don't bother with it unless you want to switch to lighttpd or nginx.
Alright
@hybinet:
Install a caching plugin for WordPress.
"The client" (my friend) has already done this.
@hybinet:
Adjust the variables that mysqltuner tells you to adjust. They are meant to give MySQL more memory to play with. This might seem to conflict with the "low memory setting" tutorial you linked to (it tells you to use 16K keybuffer whereas mysqltuner wants 39M+), but the tutorial was written for really low memory situations where you must conserve memory even at the cost of performance. Don't follow the tutorial, follow mysqltuner. After all, you can't expect MySQL to handle a large WordPress database efficiently if it is only allowed to use a few kilobytes of buffers and cache. The only one I still can't seem to satisfy is querycache_limit. I've put it as high as 256M and the query cache efficiency still stays at 0% - surely there are some result sets (indeed most) on this small WP blog that are less than 256MB! Do I need to wait until the server has been up longer for more accurate readouts, or is something wrong?
@hybinet:
Run ab again after increasing these variables as suggested.
Still hovering around 13.x reqs/sec, which I'm not complaining about. I'm telling "the client" that we'll try another switchover this coming weekend. Thanks for all of your help so far.
@rawsted:
The only one I still can't seem to satisfy is querycachelimit. I've put it as high as 256M and the query cache efficiency still stays at 0% - surely there are some result sets (indeed most) on this small WP blog that are less than 256MB! Do I need to wait until the server has been up longer for more accurate readouts, or is something wrong?
In your last mysqltuner output, query cache efficiency was 79.3%. If you're getting 0% now, it's probably because you keep restarting MySQL. Also, 256M is way too big, even for testing:o
@rawsted:
Still hovering around 13.x reqs/sec, which I'm not complaining about. I'm telling "the client" that we'll try another switchover this coming weekend. Thanks for all of your help so far.
13 req/s on a "simple" WordPress blog sounds a bit low to me, especially if you have a caching plugin. But this depends a lot on what other plugins you have, so if you're OK with it, that's fine. Definitely a huge improvement over your previous situation.
@hybinet:
In your last mysqltuner output, query cache efficiency was 79.3%. If you're getting 0% now, it's probably because you keep restarting MySQL. Also, 256M is way too big, even for testing
:o
Heheh, I guess I need to pay a little more attention to my own diagnostic output ><
@hybinet:
13 req/s on a "simple" WordPress blog sounds a bit low to me, especially if you have a caching plugin. But this depends a lot on what other plugins you have, so if you're OK with it, that's fine. Definitely a huge improvement over your previous situation.
Yeah, I'm ok with it for now seeing as the site itself hasn't been optimized outside of a caching plugin. For instance, if you run a Yslow or an audit in Chrome Dev Tools you'll see a ton of changes that could be made to CSS/Javascripts etc to speed up page load. I also installed but have not configured apc, so I'm going to look at that today to see how it works and how it's used.
@rawsted:
Yeah, I'm ok with it for now seeing as the site itself hasn't been optimized outside of a caching plugin. For instance, if you run a Yslow or an audit in Chrome Dev Tools you'll see a ton of changes that could be made to CSS/Javascripts etc to speed up page load. I also installed but have not configured apc, so I'm going to look at that today to see how it works and how it's used.
ab doesn't measure CSS/JavaScript load speed. It only measures how long it takes to generate the HTML content of the page. Listening to YSlow, etc. will improve the human perception of page load speed, but it won't change the 13 req/s figure.
APC needs very little configuration besides apc.shm_size, and the default value is quite adequate there. You might be able to squeeze out a tiny bit more performance by turning off apc.stat, but this doesn't really matter when you're doing 13 req/s, and WordPress will probably not like it. (It makes updating more complicated.)
@hybinet:
ab doesn't measure CSS/JavaScript load speed. It only measures how long it takes to generate the HTML content of the page. Listening to YSlow, etc. will improve the human perception of page load speed, but it won't change the 13 req/s figure.
Ah, of course. I played around with it a little and increasing concurrency nets a better result - 10x2000 I get 26.89 reqs/sec
@hybinet:
APC needs very little configuration besides apc.shm_size, and the default value is quite adequate there. You might be able to squeeze out a tiny bit more performance by turning off apc.stat, but this doesn't really matter when you're doing 13 req/s, and WordPress will probably not like it. (It makes updating more complicated.)
Yeah I discovered this after looking at the docs. I'm just going to leave it alone.
@rawsted:
26.89 reqs/sec
That looks much better. Good luck on your switchover!
@hybinet:
@rawsted:26.89 reqs/sec
That looks much better. Good luck on your switchover!
Thanks again for all of your help:)